The information in this article applies to: - Microsoft Excel for Windows, version 5.0 - Microsoft Excel for Windows 95, version 7.0
SUMMARY
Microsoft Excel versions 5.0 and later can read Lotus 1-2-3 worksheets. In addition, you can create a Visual Basic, Applications Edition, procedure to automatically open each Lotus 1-2-3 file in a specified directory and save it as a Microsoft Excel workbook file (.XLS).
The macro example in the "More Information" section of this article demonstrates a looping procedure you can use to perform this action. The example provided does not delete the original Lotus files, and it will prompt you if a file with the same name is about to be overwritten.
NOTE: If you are using Microsoft Excel version 5.0 or 5.0c, you need to use
the "Lotus 1-2-3 WK4 File Converter" in order to read Lotus 1-2-3 version
4.0 files. This converter is available as an Application Note. For
additional information, please see the following article(s) in the
Microsoft Knowledge Base:
ARTICLE-ID: Q122583 TITLE : XL5: AppNote: Lotus 1-2-3 WK4 File Converter (WE1130)
MORE INFORMATION
Before you run the macro, make sure the directory where the Lotus 1-2-3 files are located is set as the current directory. Click Macro on the Tools menu, and click "LotusConverter" from the list of macros, and then click Run. The macro will proceed without your intervention unless a duplicate filename is found (in which case you will be prompted to change the duplicate filename).
Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.
Visual Basic Code Example
'Begin the subroutine
Sub LotusConverter()'Set "filenam" to the first matching file in the current directory filenam = Dir("*.wk*")
'Loop to open each matching file in the current directory
Do While Len(filenam) > 0
On Error Resume Next
'Opens the file
Workbooks.Open Filename:=filenam
'Continues execution if there was no error opening the file.
If Err = 0 Then
'Creates "newname" based on original Lotus filename
newnam = Left(filenam, InStr(1, filenam, ".") - 1) & ".xls"
'Saves the new file as an Excel normal file
ActiveWorkbook.SaveAs Filename:=newnam, FileFormat:=xlNormal
'Closes the current file
ActiveWorkbook.Close
Else
'Display message if opening filenam was unsuccessful.
MsgBox "Unable to Open file: " & filenam
'Resets error checking.
Err = 0
End If
'Gets the next file name
filenam = Dir()
'Repeats the loop for all matching files in the current directory
Loop
End Sub
KBCategory: kbprg kbcode KBSubcategory: Additional reference words: 7.00 5.00 Batch converter multiple files
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 1995.